package com.iyundao.io.mapper;

import com.iyundao.io.model.*;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface WishCustomMapper {

    @Select("select w.id,w.submitnum,DATE_FORMAT(w.submittime,'%Y-%m-%d') submittime,w.submitsumintegral, " +
            "u.real_name userName,d.real_name deptName  " +
            "from wishsubmit w " +
            "INNER JOIN sys_user u " +
            "on u.sid=w.partymemberid " +
            "INNER JOIN user_dept ud " +
            "on ud.uid=u.sid " +
            "INNER JOIN sys_dept d " +
            "on d.sid=ud.did " +
            "where  u.real_name like #{realName,jdbcType=VARCHAR} and d.sid in " +
            "(select sid from sys_dept where sid=#{sid,jdbcType=CHAR} or parent_id=#{sid,jdbcType=CHAR})")
    List<WishSubmitShow> selectByLikeName(String realName, String sid);

    @Select("select w.id,w.submitnum,DATE_FORMAT(w.submittime,'%Y-%m-%d') submittime,w.submitsumintegral, " +
            "u.real_name userName,d.real_name deptName  " +
            "from wishsubmit w " +
            "INNER JOIN sys_user u " +
            "on u.sid=w.partymemberid " +
            "INNER JOIN user_dept ud " +
            "on ud.uid=u.sid " +
            "INNER JOIN sys_dept d " +
            "on d.sid=ud.did " +
            "where  w.submittime >= #{date1,jdbcType=VARCHAR} and w.submittime <= #{date2,jdbcType=VARCHAR} and " +
            "d.sid in (select sid from sys_dept where sid=#{sid,jdbcType=CHAR} or parent_id=#{sid,jdbcType=CHAR})")
    List<WishSubmitShow> selectByDate(String date1, String date2, String sid);

    @Select("select id,giftname,giftintegral,giftimg from gift where" +
            "id in (select giftid from wish where partymemberid=#{partymemberid,jdbcType=CHAR})")
    List<Wish> selectByPartymemberId(String partymemberid);

    @Delete("delete from wish where giftid=#{giftid,jdbcType=INTEGER} and partymemberid=#{partymemberid,jdbcType=CHAR}")
    int deleteSelecttive(Wish wish);

    @Select("select w.id,w.submitnum,DATE_FORMAT(w.submittime,'%Y-%m-%d') submittime,w.submitsumintegral, " +
            "u.real_name userName,d.real_name deptName  " +
            "from wishsubmit w " +
            "INNER JOIN sys_user u " +
            "on u.sid=w.partymemberid " +
            "INNER JOIN user_dept ud " +
            "on ud.uid=u.sid " +
            "INNER JOIN sys_dept d " +
            "on d.sid=ud.did " +
            "where d.sid in (select sid from sys_dept where sid=#{sid,jdbcType=CHAR} or parent_id=#{sid,jdbcType=CHAR})")
    List<WishSubmit> selectAllBySid(String sid);

    @Select("select id, giftname, giftnum, giftintegral,giftdetails, giftimg,DATE_FORMAT(gifttime,'%Y-%m-%d %H:%i:%s') gifttime, giftflag,organizationid " +
            "from gift where id in (select giftid from wish where partymemberid = #{sid,jdbcType=VARCHAR})")
    List<GiftShow> selectBySid(String sid);

    @Select("select w.submitnum,w.id,DATE_FORMAT(w.submittime,'%Y-%m-%d %H:%i:%s') submittime,sd.real_name deptName,su.real_name userName from wishsubmit w " +
            "LEFT JOIN sys_user su " +
            "ON w.partymemberid = su.sid " +
            "LEFT JOIN user_dept ud " +
            "on ud.uid = su.sid " +
            "LEFT JOIN sys_dept sd " +
            "ON sd.sid=ud.did " +
            "where sd.sid=#{id,jdbcType=VARCHAR} or sd.parent_id=#{id,jdbcType=VARCHAR}")
    List<WishSubmitShow> selectByDeptId(String id);

}
